package com.tianqizhikong.exceltool;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.metadata.ReadSheet;
/*import org.slf4j.Logger;
import org.slf4j.Logger;*/
//import org.slf4j.Logger;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *@Author: zlr
 *@CreateTime: 2024-12-30
 *@Description: 两个需求合并为一个程序
 */
public class ExcelToolApplication {
    private static final Logger log = LoggerFactory.getLogger(ExcelToolApplication.class);
    public static void main(String[] args) {
        String txtFilePath = "C:/ExcelTool/WorkFile/sequences.txt";
        List<String> list = readTxtToList(txtFilePath);
        String outputFilePathOrigin = "C:/ExcelTool/WorkFile/output/";
        String inputFilePathOrigin = "C:/ExcelTool/WorkFile/input/";
        String templateFilePath = "C:/ExcelTool/WorkFile/template.xlsx";  // 模板 Excel 路径
        String targetFilePath = "C:/ExcelTool/WorkFile/source/";      // 目标 Excel 路径
        /*String txtFilePath = "D:/ExcelTool/WorkFile/sequences.txt";
        List<String> list = readTxtToList(txtFilePath);
        String outputFilePathOrigin = "D:/ExcelTool/WorkFile/output/";
        String inputFilePathOrigin = "D:/ExcelTool/WorkFile/input/";
        String templateFilePath = "D:/ExcelTool/WorkFile/template.xlsx";  // 模板 Excel 路径
        String targetFilePath = "D:/ExcelTool/WorkFile/source/";      // 目标 Excel 路径*/
        Map<String, Integer> sheetMap = getSheetName(templateFilePath);

        //填充Excel文件
        for (int i = 0; i < list.size(); i++) {
            String s = list.get(i);
            s = s.substring(0, s.length() - 5);
            String targetFile= targetFilePath+ s + ".xlsx";
            log.info("开始对"+s+".xlsx文件进行填充");
            Integer sheetNo = sheetMap.get(s);
            if (sheetNo != null){
                updateTargetData(templateFilePath, targetFile,s);
            }
        }

        //对库存和需求进行计算
        List<List<TargetModel>> allExcelData = getAllExcelData(inputFilePathOrigin,list);
        Map<Integer, Map<String, CalculateData>> calCulExcelData = getCalCulExcelData(allExcelData,list);
        List<List<TargetModel>> lists = matchAndCalCul(allExcelData, calCulExcelData,list);

        //把计算后的数据写入到Excel中
        for (int i = 0; i < lists.size(); i++) {
            String outputFilePath = outputFilePathOrigin + list.get(i);
            log.info("开始写入第" + i + "个Excel文件, 文件路径为"+outputFilePath);
            EasyExcel.write(outputFilePath, TargetModel.class).sheet("Updated Targets").doWrite(lists.get(i));
        }
    }


    /**
     * 库存、需求计算
     * */
    public static List<List<TargetModel>> matchAndCalCul(List<List<TargetModel>> sourceList, Map<Integer,Map<String, CalculateData>> allCalCulData, List<String> fieldNames) {
        log.info("开始计算库存、需求...");
        List<Map<String, CalculateData>> calculedDataList = new ArrayList<>();
        calculedDataList.add(0,allCalCulData.get(0));
        //需求中的库存、毛需求的计算都是基于前面的Excel取差值获得，第一个Excel没有前置Excel直接跳过
        for (int i = 1; i < sourceList.size(); i++) {
            //这一层对所有Excel文件进行遍历
            List<TargetModel> source = sourceList.get(i);

            for (TargetModel TargetModel : source) {//这一层对单个Excel里的全部数据进行遍历
                //判断物料编码是否重复
                for (Map<String, CalculateData> singleCalCulDataMap : calculedDataList) { //对已经遍历过的Excel表的数据进行遍历
                    if (singleCalCulDataMap.containsKey(TargetModel.getMaterialCode())) {
                        Integer demand = singleCalCulDataMap.get(TargetModel.getMaterialCode()).getDemand();
                        if (demand != null && TargetModel.getAvailableStock() != null){
                            String fileName = singleCalCulDataMap.get(TargetModel.getMaterialCode()).getFileName();
                            System.out.println("物料编码："+TargetModel.getMaterialCode() + "------" + fieldNames.get(i)+ " - " + fileName + ": " + TargetModel.getAvailableStock() + "-" + demand + "=" + (TargetModel.getAvailableStock() - demand));
                            log.info("物料编码："+TargetModel.getMaterialCode() + "------" + fieldNames.get(i)+ " - " + fileName + ": " + TargetModel.getAvailableStock() + "-" + demand + "=" + (TargetModel.getAvailableStock() - demand));
                            int stock = TargetModel.getAvailableStock() - demand;
                            //因张婷要求如果库存为负数，将库存置为0
                            if (TargetModel.getAvailableStock() <= 0 || stock <= 0){
                                TargetModel.setAvailableStock(0);
                                continue;
                            }
                            TargetModel.setAvailableStock(stock);
                        }else {
                            if (demand == null) continue;
                            System.out.println(fieldNames.get(i)+"文件物料编码为："+TargetModel.getMaterialCode()+",该行记录可用库存为空, 调整为库存为0进行计算" + "计算后可用库存为："+(-demand));
                            log.info(fieldNames.get(i)+"文件物料编码为："+TargetModel.getMaterialCode()+",该行记录可用库存为空, 调整为库存为0进行计算" + "计算后可用库存为："+(-demand));
                            TargetModel.setAvailableStock(-demand);
                        }
                    }
                }
            }
            //获取已经遍历过的Excel表的库存、需求数据
            calculedDataList.add(i,allCalCulData.get(i));
        }
        return sourceList;
    }


/*    public static Map<String,Map<String, CalculateData>> sourceList2Map(List<List<TargetModel>> list, List<String> fieldNames) {
        Map<String,Map<String, CalculateData>> result = new HashMap<>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, CalculateData> ele = new HashMap<>();
            for (TargetModel TargetModel : list.get(i)) {
                ele.put(TargetModel.getMaterialCode(),new CalculateData(TargetModel.getAvailableStock(),TargetModel.getGrossDemand()));
            }
            result.put(fieldNames.get(i),ele);
        }
        return result;
    }*/


    /**
     * @param filePathList Excel文件路径列表
     * @return List<List<TargetModel>> 所有Excel文件<单个Excel文件<单个Excel文件里的全部数据>>
     * */
    public static List<List<TargetModel>> getAllExcelData(String originPath,List<String> filePathList) {
        log.info("开始读取Excel文件...");
        List<List<TargetModel>> result = new ArrayList<>();
        for (String filePath : filePathList) {
            result.add(EasyExcel.read(originPath+filePath).head(TargetModel.class).sheet().doReadSync());
        }
        return result;
    }


    /**
     * 对库存、毛需求进行差值计算
     * @param   list 所有Excel文件数据
     * @return  Map<Integer,Map<String, CalCulData>>  <Excel文件顺序，<物料编码, 毛需求和库存对象>
     * */
    public static Map<Integer,Map<String, CalculateData>> getCalCulExcelData(List<List<TargetModel>> list, List<String> fieldNames) {
        log.info("开始对库存、毛需求进行差值计算...");
        Map<Integer,Map<String, CalculateData>> resList = new HashMap();
        Integer No = 0;
        for (List<TargetModel> TargetModels : list) {
            HashMap<String, CalculateData> eleMap = new HashMap<>();
            for (TargetModel TargetModel : TargetModels) {
                eleMap.put(TargetModel.getMaterialCode(), new CalculateData(fieldNames.get(No), TargetModel.getAvailableStock(), TargetModel.getGrossDemand()));
            }
            resList.put(No++,eleMap);
        }
        return resList;
    }

    public static void updateTargetData(String sourceFile, String targetFile,String sheetName) {
        log.info("开始更新Excel文件...");
        log.info("读取 source 数据...");
        // 1. 读取 source 数据
        List<SourceModel> sourceList = EasyExcel
                .read(sourceFile)
                .head(SourceModel.class)
                .sheet(sheetName)
                .doReadSync();

        // 2. 将源数据存入物料编码为键的 Map 中（物料编码为唯一标识）
        Map<String, SourceModel> sourceMap = new HashMap<>();
        for (SourceModel source : sourceList) {
            sourceMap.put(source.getMaterialCode(), source);
        }

        log.info("读取 target 数据...");
        // 3. 读取 target 数据
        List<TargetModel> targetList = EasyExcel
                .read(targetFile)
                .head(TargetModel.class)
                .sheet(0).doReadSync();
        log.info("开始匹配并填充 target 数据...");
        // 4. 根据物料编码匹配并填充 target 数据
        for (TargetModel target : targetList) {
            SourceModel matchedSource = sourceMap.get(target.getMaterialCode());
            if (matchedSource != null) {
                // 如果匹配成功，将 source 的数据填充到 target 中
                target.setSupplier(matchedSource.getSupplier());
                target.setPersonInCharge(matchedSource.getResponsiblePerson());
                target.setMaterialLeadTime(matchedSource.getMaterialDeliveryDate());
                target.setExceptionReason(matchedSource.getRemarksExceptionReason());
                target.setPmcResponse(matchedSource.getPmcReply());
            }
        }

        // 5. 将更新后的 target 数据写入到新的 Excel 文件
        //EasyExcel.write("C:/ExcelTool/WorkFile/input/"+sheetName+".xlsx", TargetModel.class).sheet("Updated Targets").doWrite(targetList);
        EasyExcel.write("D:/ExcelTool/WorkFile/input/"+sheetName+".xlsx", TargetModel.class).sheet("Updated Targets").doWrite(targetList);
        System.out.println("填充后的数据已保存到 " + targetFile+ ".xlsx文件中");
        log.info("填充后的数据已保存到 " + targetFile+ ".xlsx文件中");
    }

    /**
     * 获取Excel工作区sheet名
     * */
    public static Map<String, Integer> getSheetName(String fileName){
        log.info("开始获取Excel工作区sheet名...");
        Map<String, Integer> map = new HashMap<>();
        Integer i = 0;
        List<ReadSheet> readSheets = EasyExcel.read(fileName).build().excelExecutor().sheetList();
        for (ReadSheet readSheet : readSheets) {
            map.put(readSheet.getSheetName(),i++);
        }
        return map;
    }



    /**
     * 获取txt文件内容
     * */
    public static List<String> readTxtToList(String filePath) {
        log.info("开始读取txt文件...");
        List<String> lines = new ArrayList<>();
        try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
            String line;
            while ((line = br.readLine()) != null) {
                lines.add(line); // 每行作为一个元素加入 List
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return lines;
    }
}
